﻿using System;
using System.Collections.Generic;
using System.Text;
using Cashier_DAO.db;
using Cashier_Model;
using System.Collections;
using System.Data;
using System.Data.Common;

namespace Cashier_DAO
{
    public class CategoryDAO: DAOFactory
    {
        /// <summary>
        /// 查询商品分类信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public Category GetCategory(DbConnection conn, Category category)
        {
            Category result = null;
            try
            {
                StringBuilder sb = new StringBuilder("select c.*,(select name from category a where c.parent_id=a.id) as parentname from category c where c.id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, category);

                if (paramtable.Keys.Count > 0)
                {
                    DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                    this.AddCommandParamters(cmd, paramtable);
                    DataTable dt = new DataTable("category");
                    DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                    da.Fill(dt);

                    if (dt != null && dt.Rows.Count == 1)
                    {
                        DataRow row = dt.Rows[0];
                        result = new Category();
                        this.DataRowToModel(row, result);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 查询分类商品信息列表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public DataTable GetCategoryTable(DbConnection conn, Category category)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder("select c.*,(select name from category a where c.parent_id=a.id) as parentname from category c where c.id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, category);
                if (category.Parentid >= 0)
                {
                    sb.Append(" and parent_id=@parentid");
                    paramtable.Add("parentid", category.Parentid);
                }
                sb.Append(" order by c.orderno asc");

                if (category.Pagesize > 0)
                {
                    sb.Append(" limit @offset,@pagesize");
                    paramtable.Add("offset", (category.Pageindex - 1) * category.Pagesize);
                    paramtable.Add("pagesize", category.Pagesize);
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("category");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 查询商品分类记录数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public long GetCategoryRecord(DbConnection conn, Category category)
        {
            long record = 0;
            try
            {
                StringBuilder sb = new StringBuilder("select count(c.id) as c from category c where c.id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, category);
                if (category.Parentid >= 0)
                {
                    sb.Append(" and parent_id=@parentid");
                    paramtable.Add("parentid", category.Parentid);
                }
                
                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                Object obj = cmd.ExecuteScalar();
                if (obj != null)
                    record = (long)obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return record;
        }

        /// <summary>
        /// 插入商品分类信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public long InsertCategory(DbConnection conn, Category category)
        {
            long id = 0;
            try
            {
                id = (long)this.Insert(conn, category);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        /// <summary>
        /// 更新商品分类信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public int UpdateCategory(DbConnection conn, Category category)
        {
            int flag = 0;
            try
            {
                flag = this.Update(conn, category);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 删除商品分类
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public int DeleteCategory(DbConnection conn, Category category)
        {
            int count = 0;
            try
            {
                count = this.Delete(conn, category);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }

        /// <summary>
        /// 判断是否存在同名的分类名称
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public bool ExistCategoryName(DbConnection conn, Category category)
        {
            bool exist = true;
            try
            {
                StringBuilder sb = new StringBuilder("select count(id) as c from category where name=@name");
                Hashtable paramtable = new Hashtable();
                paramtable.Add("name", category.Name);
                if (category.Parentid > 0)
                {
                    sb.Append(" and parent_id=@parentid");
                    paramtable.Add("parentid", category.Parentid);
                }
                if (category.Id > 0)
                {
                    sb.Append(" and id!=@id");
                    paramtable.Add("id", category.Id);
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                object obj = cmd.ExecuteScalar();
                if (obj == null)
                    exist = false;
                else
                {
                    int count = Int32.Parse(obj.ToString());
                    if (count > 0)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return exist;
        }
    }
}
